﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace template_dll
{
    public class SPparams
    {
        string _name;
        object _value;

        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }

        public object Value
        {
            get { return _value; }
            set { _value = value; }
        }
    }

    public class SqlStorProc
    {

        public int SqlProcExecNonQuery(SqlConnection con, string procname, int timeout, SPparams[] param)
        {
            int res = -1;
            try
            {
                if (con.State == ConnectionState.Closed) con.Open();
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (SPparams p in param)
                    {
                        com.Parameters.AddWithValue(p.Name, p.Value);
                    }
                }
                res = com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("int SqlProcExecNonQuery(SqlConnection con, string procname, int timeout, sp_params[] param)\n" + ex.Message);
            }
            finally
            {
                if (con.State != ConnectionState.Closed) con.Close();
            }
            return res;
        }

        public DataTable[] SqlProcExecWithQuery(SqlConnection con, string procname, int timeout, SPparams[] param)
        {
            DataTable[] dt = new DataTable[0];
            try
            {
                if (con.State == ConnectionState.Closed) con.Open();
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (SPparams p in param)
                    {
                        com.Parameters.AddWithValue(p.Name, p.Value);
                    }
                }
                SqlDataReader dr = com.ExecuteReader();

                while (dr.HasRows)
                {
                    Array.Resize(ref dt, dt.Length + 1);
                    dt[dt.Length - 1] = new DataTable();
                    dt[dt.Length - 1].Load(dr);
                    if (dt[dt.Length - 1].Columns.Count > 2) dt[dt.Length - 1].TableName = dt[dt.Length - 1].Rows[0][1].ToString();
                    if (dr.IsClosed) break;
                }
                dr.Close();

            }
            catch (Exception ex)
            {
                throw new Exception("int SqlProcExecNonQuery(SqlConnection con, string procname, int timeout, sp_params[] param)\n" + ex.Message);
            }
            finally
            {
                if (con.State != ConnectionState.Closed) con.Close();
            }
            return dt;
        }

        public DataTable SqlProcExecWithQueryOneTable(SqlConnection con, string procname, int timeout, SPparams[] param)
        {
            DataTable dt = new DataTable();
            try
            {
                if (con.State == ConnectionState.Closed) con.Open();
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (SPparams p in param)
                    {
                        com.Parameters.AddWithValue(p.Name, p.Value);
                    }
                }
                dt.Load(com.ExecuteReader());
            }
            catch (Exception ex)
            {
                throw new Exception("int SqlProcExecNonQuery(SqlConnection con, string procname, int timeout, sp_params[] param)\n" + ex.Message);
            }
            finally
            {
                if (con.State != ConnectionState.Closed) con.Close();
            }
            return dt;
        }

        public object SqlProcExecScalar(SqlConnection con, string procname, int timeout, SPparams[] param)
        {
            object res = null;
            try
            {
                if (con.State == ConnectionState.Closed) con.Open();
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (SPparams p in param)
                    {
                        com.Parameters.AddWithValue(p.Name, p.Value);
                    }
                }
                res = com.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception("int SqlProcExecNonQuery(SqlConnection con, string procname, int timeout, sp_params[] param)\n" + ex.Message);
            }
            finally
            {
                if (con.State != ConnectionState.Closed) con.Close();
            }
            return res;
        }
    }
}
